package com.ysq.nfd.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.ysq.nfd.config.DbSource;

public class MysqlSaver {

	
	public static Connection getMysqlCon() throws SQLException{
		
		String dbUrl = "jdbc:mysql://localhost:3306/kettle_test";
		
		String dbUser = "root";
		String dbPassword = "admin";
		
		Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
		
		return con;
	}
	
	public static int getMaxId(Connection con){
		int maxId = 0;
		String sql = "select max(user_id) from user_info";
		
		Statement stmt = null;
		ResultSet rst = null;
		
		try {
			stmt = con.createStatement();
			rst = stmt.executeQuery(sql);
			
			if(rst.next()){
				
				maxId = rst.getInt(1);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			DbSource.closeDb(null, stmt, rst);
			
		}
		return maxId;
	}
	
	public static void testSave(){
		
		String sql = "insert into user_info (user_id, user_name, age, email, mark) values (?, ?, ?, ?, ?) ";
		
		Connection con = null;
		PreparedStatement pst = null;
		
		try {
			con = getMysqlCon();
			
			int maxId = getMaxId(con);
			
			con.setAutoCommit(false);
			
			pst = con.prepareStatement(sql);
			
			for(int i=maxId + 1; i<1000; i++){
				try {
					pst.setInt(1, i);
					pst.setString(2, "userName " + i);
					pst.setInt(3, i);
					pst.setString(4, "email : " + i);
					pst.setString(5, "mark " + i);
					
					pst.addBatch();
					
					if(i%10 == 0){
						pst.executeBatch();
						con.commit();
						System.out.println("do a commit ... " + i);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
			pst.executeBatch();
			con.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				con.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			DbSource.closeDb(con, pst, null);
			
		}
		
	}
	
	public static void likeTest(){
		
		String sql = "select * from user_info where user_name like ? limit 0,10 ";
		
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rst = null;
		
		try {
			con = getMysqlCon();
			pst = con.prepareStatement(sql);
			
			String likePart = "%user%";
			pst.setString(1, likePart);
			
			rst = pst.executeQuery();
			
			while(rst.next()){
				
				String userName = rst.getString("user_name");
				
				System.out.println(userName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbSource.closeDb(con, pst, null);
		}
		
	}
	
	
	public static void main(String[] args) {
		
		likeTest();
		
		System.out.println("finished ... ");
		
	}
	
}
